# !/usr/bin/env python
# -*- coding:utf-8 -*-
# project name: agent_interface_automation
# author: "Lei Yong"
# creation time: 2020-11-26 15:16
# Email: leiyong711@163.com


from sqlite_pool import sqlite3, Pool

# 创建接口管理、商品ID、bannerID表
CREATE_INTERFACE_TABLE_SQL = "create table interface (id integer primary key,url varchar(255) UNIQUE,method varchar(15) NOT NULL,request_data TEXT,headers TEXT NOT NULL,response_result TEXT,struct_verify TEXT,create_time TIMESTAMP default (datetime('now', 'localtime')))"
CREATR_ITEM_ID_TABLE_SQL = "create table ItemId (id integer primary key,item_id varchar(255) UNIQUE,create_time TIMESTAMP default (datetime('now', 'localtime')))"
CREATE_BANNER_ID_TABLE_SQL = "create table BannerId (id integer primary key,banner_id varchar(255) UNIQUE NOT NULL,create_time TIMESTAMP default (datetime('now', 'localtime')))"

# 插入商品接口、商品ID、bannerID表数据
INSERT_INTERFACE_DATA_SQL = 'insert into interface (url, method, request_data, headers, response_result, struct_verify) values (?,?,?,?,?,?)'
INSERT_ITEM_ID_DATA_SQL = 'insert into ItemId (item_id) values ("%s")'
INSERT_BANNER_ID_DATA_SQL = 'insert into BannerId (banner_id) values ("%s")'


# SQLITE数据库操作
class DB:

    def __init__(self):
        # 初始化数据库
        self.pool = Pool(database="interfaceTest.db")
        try:
            conn = self.pool.get()
            conn.execute(CREATE_INTERFACE_TABLE_SQL)
            print("\033[1;32m INTERFACE 数据表创建成功 \033[0m")
        except sqlite3.OperationalError as e:
            print(f"\033[1;31m 创建 INTERFACE 表异常 {e} \033[0m")

        try:
            conn = self.pool.get()
            conn.execute(CREATR_ITEM_ID_TABLE_SQL)
            print("\033[1;32m ITEM_ID 数据表创建成功 \033[0m")
        except sqlite3.OperationalError as e:
            print(f"\033[1;31m 创建 ITEM_ID 表异常 {e} \033[0m")

        try:
            conn = self.pool.get()
            conn.execute(CREATE_BANNER_ID_TABLE_SQL)
            print("\033[1;32m BANNER_ID 数据表创建成功 \033[0m")
        except sqlite3.OperationalError as e:
            print(f"\033[1;31m 创建 BANNER_ID 表异常 {e} \033[0m")

    def inset_interface(self, url, method, request_data, headers, response_result, struct_verify):
        # 插入接口数据
        try:
            conn = self.pool.get()
            conn.execute(INSERT_INTERFACE_DATA_SQL, (url, method, str(request_data), str(headers), str(response_result), struct_verify))
            conn.commit()
            print("\033[1;32m INTERFACE 数据插入成功 \033[0m")
        except sqlite3.IntegrityError:
            print("\033[1;33m INTERFACE 数据已存在 \033[0m")

    def inset_item_id(self, item_id):
        # 插入商品 ID 数据
        try:
            print(item_id)
            conn = self.pool.get()
            conn.execute(INSERT_ITEM_ID_DATA_SQL % item_id)
            conn.commit()
            print("\033[1;32m ITEM_ID 数据插入成功 \033[0m")
        except sqlite3.IntegrityError:
            print("\033[1;33m ITEM_ID 数据已存在 \033[0m")

    def inset_banner_id(self, banner_id):
        # 插入Banner ID 数据
        try:
            conn = self.pool.get()
            conn.execute(INSERT_BANNER_ID_DATA_SQL % banner_id)
            conn.commit()
            print("\033[1;32m BANNER_ID 数据插入成功 \033[0m")
        except sqlite3.IntegrityError:
            print("\033[1;33m BANNER_ID 数据已存在 \033[0m")

    def dict_factory(self, connrsor, row):
        """
        以键值对方式返回数据库数据
        :param connrsor:
        :param row:
        :return:
        """
        d = {}
        for idx, col in enumerate(connrsor.description):
            d[col[0]] = row[idx]
        return d

    def get_db_data(self, sql):
        """
        获取数据库数据
        :param sql:
        :return:
        """
        conn = self.pool.get()
        result = conn.execute(sql)
        temp = []
        for i in result:
            temp.append(i)
        return temp
